SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TXMVTO_CNTB_Q31]
/*-------------------------------------------------------------------*/
/*----- Empresa           : OFISIS S.A.                          ----*/
/*----- Cliente           : OFISIS S.A.                          ----*/
/*----- Sistema           : Contabilidad                         ----*/
/*----- Modulo            : Administracion                      ----*/
/*----- Programa          : PDT  IGV Retenciones  ----*/
/*----- Nombre            : SP_TXMVTO_CNTB_Q1                   ----*/
/*----- Script            : TXMVTO_CNTB                          ----*/
/*----- Desarrollado por  : Martin Garcia Farfan                 ----*/
/*----- Fecha             : 27/05/2002                           ----*/
/*----- Base de Datos     : MS SQL Server                        ----*/
/*----- Version BD        : 7.0                                  ----*/
/*-------------------------------------------------------------------*/
/*--------------------------------------------------------------------*/
/*----- Modificado 1 por : Percy Garcia                          ----*/
/*----- Fecha Modifica   : 10/01/2004                            ----*/
/*----- Detalle Modifica : Migracion ver 5.0                     ----*/
/*-------------------------------------------------------------------*/
/*-------------------------------------------------------------------*/
/*----- Drop proc SP_TXMVTO_CNTB_Q31                             ----*/
/*----- Grant all on SP_TXMVTO_CNTB_Q31 to PUBLIC                ----*/
/*----- Exec SP_TXMVTO_CNTB_Q31 '01', 1, 2003, 12                ----*/
/*-------------------------------------------------------------------*/

@ISCO_EMPR       TD_VC_002,
@INNU_CNTB_EMPR  TD_IN_001,
@INNU_ANNO       TD_IN_004_ANNO,
@INNU_MESE       TD_IN_002_MESE

As

Declare
@CSCO_UNID_CNTB  TD_VC_003,
@CSCO_OPRC_CNTB  TD_VC_003,
@CSNU_ASTO       TD_VC_010,
@CDFE_ASTO_CNTB  TD_DT_001,
@CSTI_AUXI_EMPR  TD_VC_001,
@CSCO_AUXI_EMPR  TD_VC_020,
@CSNO_AUXI       TD_VC_100,
@CSTI_DOCU       TD_VC_003,
@CSNU_DOCU       TD_VC_020,
@CDFE_DOCU       TD_DT_001,
@CNIM_MVTO_ORIG  TD_NU_016_004,
@CNIM_MVTO_CNTB  TD_NU_016_004,
@CSTI_DOCU_REFE  TD_VC_003,
@CSNU_DOCU_REFE  TD_VC_020,
@CDFE_DOCU_REFE  TD_DT_001,
@CNIM_MVTO_RETE  TD_NU_016_004,
@VNNU_ANNO       TD_IN_004_ANNO,
@VNNU_MESE       TD_IN_002_MESE,
@VSCO_OPRC_CNTB  TD_VC_003,
@VDFE_ASTO_CNTB  TD_DT_001,
@VSNU_ASTO       TD_VC_010,
@VSTI_DOCU       TD_VC_003,
@VSNU_DOCU       TD_VC_020,
@VDFE_DOCU       TD_DT_001,
@VNIM_MVTO_CNTB  TD_NU_016_004,
@VSCO_IIGV_RETE  TD_VC_020,
@CSNU_RUCS       TD_VC_020,
@CSRA_SOCI       Varchar(50),
@CSAP_PATE       TD_VC_050, 
@CSAP_MATE       TD_VC_050, 
@CSNO_NAT1       TD_VC_020, 
@CSNO_NAT2       TD_VC_020,
@CSTI_COMP       TD_VC_003,
@CSCO_SUNA       TD_VC_003,
@VNNU_LONG       Integer,
@VNNU_LONG_SERI  Integer,
@VNNU_LONG_0001  Integer,
@VNNU_LONG_S001  Integer ,
@CSTI_PER1       TD_VC_001,
@VNSU_TOTA       TD_NU_016_004,
@VSNU_RUCS       TD_VC_020,
@VSNU_DOCU_REFE  TD_VC_020,
@CNIM_MVTO_PROV  Numeric(16,4),
@CDFE_PROV       Datetime,
@VSID_0002       TD_VC_003, 
@VSID_COMP       TD_VC_003, 
@VSID_IGVV_RETE  TD_VC_003

-- Tabla Temporal
Create Table #TXMVTO_CNTB_Q29
(TI_AUXI_EMPR  Varchar(01)   NULL,
 CO_AUXI_EMPR  Varchar(20)   NULL,
 NO_AUXI       Varchar(100)  NULL,
 FE_PAGO       Datetime      NULL,
 TI_DOCU       Varchar(03)   NULL,
 NU_DOCU       Varchar(20)   NULL,
 IM_MVTO_PROV  Numeric(16,4) NULL,
 IM_MVTO_PAGO  Numeric(16,4) NULL,
 IM_MVTO_RETE  Numeric(16,4) NULL,
 CO_UNID_CNTB  Varchar(03)   NULL,
 CO_OPRC_CNTB  Varchar(03)   NULL,
 NU_ASTO       Varchar(10)   NULL,
 TI_DOCU_REFE  Varchar(03)   NULL,
 NU_DOCU_REFE  Varchar(20)   NULL,
 FE_DOCU_REFE  Datetime      NULL,
 NU_RUCS       Varchar(20)   NULL ,
 NO_RAZO_SOCI  Varchar(50)   NULL,
 AP_PATE_AUXI  Varchar(50)   NULL,
 AP_MATE_AUXI  Varchar(50)   NULL,
 NO_NATU_AUX1  Varchar(20)   NULL ,
 NO_NATU_AUX2  Varchar(20)   NULL,
 CO_SUNA       Varchar(02)   NULL,
 TI_PER        Varchar(01),
 SU_TOTA       Numeric(16,4) NULL,
 FE_PROV       Datetime      NULL) 

 -- Borro la tabla TWPDTS_RETE
 Delete TWPDTS_RETE

 -- Cuenta de IGV Retencion
/* Select @VSCO_IIGV_RETE = CO_CNTA_EMPR
 From TTIDEN_CNTA
 Where CO_EMPR = @ISCO_EMPR
 And NU_CNTB_EMPR = @INNU_CNTB_EMPR
 And ID_CNTA = '024'*/

 Select @VSID_0002 = ID_0002,  -- OPERACION CANCELACIONES 
 @VSID_COMP = ID_COMP,  -- ID REG COMPRAS 
 @VSID_IGVV_RETE = ID_0001 -- ID IGV RETENCION 
 From TTCNTB_EMPR Where CO_EMPR = @ISCO_EMPR 
 And NU_CNTB_EMPR = @INNU_CNTB_EMPR

 Declare CU_TXMVTO_CNTB_Q29_1 Cursor For
 Select t1.CO_UNID_CNTB, t1.CO_OPRC_CNTB, t1.NU_ASTO, t1.FE_ASTO_CNTB, t1.TI_AUXI_EMPR, 
          t1.CO_AUXI_EMPR, t2.NO_AUXI,  t1.TI_DOCU, t1.NU_DOCU, t1.FE_DOCU, 
          t1.IM_MVTO_ORIG, t1.IM_MVTO_CNTB, t1.TI_DOCU_REFE, t1.NU_DOCU_REFE, 
          t1.FE_DOCU_REFE, t1.IM_MVTO_RETE , t2.NU_RUCS, t2.NO_RAZO_SOCI, t2.AP_PATE_AUXI,
         t2.AP_MATE_AUXI, t2.NO_NATU_AUX1, t2.NO_NATU_AUX2, t3.CO_SUNA ,t2.TI_PER
 From TXMVTO_CNTB t1, TMAUXI_EMPR t2, TTDOCU_CNTB t3, TTIDEN_CNTA t4
 Where t1.CO_EMPR = @ISCO_EMPR
 And t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR
 And t1.NU_ANNO = @INNU_ANNO
 And t1.NU_MESE = @INNU_MESE
 And t1.CO_OPRC_CNTB = @VSID_0002--'020' -- Operacion donde se registran las cancelaciones
 And t1.NU_ASTO In (Select t1.NU_ASTO
                    From TXMVTO_CNTB t1, TTIDEN_CNTA t2  
                    Where t1.CO_EMPR = @ISCO_EMPR
                    And t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR
                    And t1.NU_ANNO = @INNU_ANNO
                    And t1.NU_MESE = @INNU_MESE
                    And t1.CO_OPRC_CNTB = @VSID_0002--'020' -- Operacion donde se registran las cancelaciones
/*                    And t1.CO_CNTA_EMPR In (Select CO_CNTA_EMPR
                                           From TTIDEN_CNTA
                                           Where CO_EMPR = @ISCO_EMPR
                                             And NU_CNTB_EMPR = @INNU_CNTB_EMPR
                                             And ID_CNTA = '024')*/ 
                    And t1.SI_MVTO_CNTB = 'APR'
		    And t2.CO_EMPR = t1.CO_EMPR 
		    And t2.NU_CNTB_EMPR = t1.NU_CNTB_EMPR 
                    And t2.ID_CNTA = @VSID_IGVV_RETE --'024'
                    And t2.NU_ANNO = 9999
		    And t2.CO_CNTA_EMPR = t1.CO_CNTA_EMPR )
/* And t1.CO_CNTA_EMPR In (Select CO_CNTA_EMPR
 From TTIDEN_CNTA
 Where CO_EMPR = @ISCO_EMPR
 And ID_CNTA = '001')*/
 And t4.CO_EMPR = t1.CO_EMPR 
 And t4.NU_CNTB_EMPR = t1.NU_CNTB_EMPR
 And t4.ID_CNTA = @VSID_COMP --'001'
 And t4.NU_ANNO = 9999
 And t4.CO_CNTA_EMPR = t1.CO_CNTA_EMPR
 And t1.NU_DOCU_REFE IS NOT NULL
 And t1.SI_MVTO_CNTB = 'APR'
 And t2.CO_EMPR = t1.CO_EMPR
 And t2.TI_AUXI_EMPR = t1.TI_AUXI_EMPR
 And t2.CO_AUXI_EMPR = t1.CO_AUXI_EMPR
 And t3.TI_DOCU  = t1.TI_DOCU
 Order by t1.CO_UNID_CNTB, t1.CO_OPRC_CNTB, t1.NU_ASTO

Open CU_TXMVTO_CNTB_Q29_1
Fetch CU_TXMVTO_CNTB_Q29_1 into @CSCO_UNID_CNTB, @CSCO_OPRC_CNTB, @CSNU_ASTO, 
  @CDFE_ASTO_CNTB, @CSTI_AUXI_EMPR, @CSCO_AUXI_EMPR, @CSNO_AUXI, 
  @CSTI_DOCU, @CSNU_DOCU, @CDFE_DOCU, @CNIM_MVTO_ORIG, @CNIM_MVTO_CNTB, 
  @CSTI_DOCU_REFE, @CSNU_DOCU_REFE, @CDFE_DOCU_REFE, @CNIM_MVTO_RETE , @CSNU_RUCS, @CSRA_SOCI ,@CSAP_PATE, @CSAP_MATE, @CSNO_NAT1, @CSNO_NAT2,@CSCO_SUNA ,@CSTI_PER1

While @@Fetch_Status = 0
 Begin

 -- Select para obtener la provision inicial
    Select @VNNU_ANNO = t1.NU_ANNO, 
           @VNNU_MESE = t1.NU_MESE, 
           @VSCO_OPRC_CNTB = t1.CO_OPRC_CNTB, 
           @VDFE_ASTO_CNTB = t1.FE_ASTO_CNTB,
           @VSNU_ASTO = t1.NU_ASTO, 
           @VSTI_DOCU = t1.TI_DOCU, 
           @VSNU_DOCU = t1.NU_DOCU, 
           @VDFE_DOCU = t1.FE_DOCU, 
           @VNIM_MVTO_CNTB = t1.IM_MVTO_CNTB
     From TXMVTO_CNTB t1, TTIDEN_OPER t2
     Where t1.CO_EMPR = @ISCO_EMPR
     And t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR
     And t1.NU_ANNO = @INNU_ANNO
     And t1.NU_MESE <= @INNU_MESE
/*     And t1.CO_OPRC_CNTB In (Select CO_OPRC_CNTB
                           From TTIDEN_OPER
                           Where CO_EMPR = @ISCO_EMPR
                           And NU_CNTB_EMPR = @INNU_CNTB_EMPR
                           And ID_OPRC = '001') */

     And t2.CO_EMPR = t1.CO_EMPR 
     And t2.NU_CNTB_EMPR = t1.NU_CNTB_EMPR
     And t2.ID_OPRC = @VSID_COMP--'001'
     And t2.CO_OPRC_CNTB = t1.CO_OPRC_CNTB
     And t1.TI_AUXI_EMPR = @CSTI_AUXI_EMPR
     And t1.CO_AUXI_EMPR = @CSCO_AUXI_EMPR
     And t1.TI_DOCU = @CSTI_DOCU
     And t1.NU_DOCU = @CSNU_DOCU
     And t1.FE_DOCU In (Select Min(t1.FE_DOCU)
                     From TXMVTO_CNTB t1, TTIDEN_OPER t2
                     Where t1.CO_EMPR = @ISCO_EMPR
                     And t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR
                     And t1.NU_ANNO = @INNU_ANNO
                     And t1.NU_MESE <= @INNU_MESE
/* And t1.CO_OPRC_CNTB In (Select CO_OPRC_CNTB
                                        from TTIDEN_OPER
                                       Where CO_EMPR = @ISCO_EMPR
                                       and NU_CNTB_EMPR = @INNU_CNTB_EMPR
                                       and ID_OPRC = '001') */
                     And t2.CO_EMPR = t1.CO_EMPR 
                     And t2.NU_CNTB_EMPR = t1.NU_CNTB_EMPR
                     And t2.ID_OPRC = @VSID_COMP --'001'
                     And t2.CO_OPRC_CNTB = t1.CO_OPRC_CNTB
                     And t1.TI_AUXI_EMPR = @CSTI_AUXI_EMPR
                     And t1.CO_AUXI_EMPR = @CSCO_AUXI_EMPR
                     And t1.TI_DOCU = @CSTI_DOCU
                     And t1.NU_DOCU = @CSNU_DOCU
                     And t1.SI_MVTO_CNTB = 'APR')
    And t1.SI_MVTO_CNTB = 'APR'

    --- Luego de Obtener la provision se procede a insertar los valores en la tabla temporal
    Insert into #TXMVTO_CNTB_Q29
    (TI_AUXI_EMPR, CO_AUXI_EMPR, NO_AUXI,  FE_PAGO, TI_DOCU, NU_DOCU, IM_MVTO_PROV, 
     IM_MVTO_PAGO, IM_MVTO_RETE, CO_UNID_CNTB, CO_OPRC_CNTB, NU_ASTO, TI_DOCU_REFE,
     NU_DOCU_REFE, FE_DOCU_REFE, NU_RUCS , NO_RAZO_SOCI , AP_PATE_AUXI,
     AP_MATE_AUXI, NO_NATU_AUX1 ,  NO_NATU_AUX2,CO_SUNA ,TI_PER, FE_PROV) 
    Values(@CSTI_AUXI_EMPR, @CSCO_AUXI_EMPR, @CSNO_AUXI,  @CDFE_ASTO_CNTB, 
     @CSTI_DOCU, @CSNU_DOCU, @VNIM_MVTO_CNTB, @CNIM_MVTO_CNTB, @CNIM_MVTO_RETE, 
     @CSCO_UNID_CNTB, @CSCO_OPRC_CNTB, @CSNU_ASTO, @CSTI_DOCU_REFE, 
     @CSNU_DOCU_REFE, @CDFE_DOCU_REFE, @CSNU_RUCS, @CSRA_SOCI, @CSAP_PATE, @CSAP_MATE, @CSNO_NAT1, @CSNO_NAT2,@CSCO_SUNA , 
     @CSTI_PER1, @VDFE_DOCU)
   
   Fetch CU_TXMVTO_CNTB_Q29_1 into @CSCO_UNID_CNTB, @CSCO_OPRC_CNTB, @CSNU_ASTO, 
    @CDFE_ASTO_CNTB, @CSTI_AUXI_EMPR, @CSCO_AUXI_EMPR, @CSNO_AUXI,
    @CSTI_DOCU, @CSNU_DOCU, @CDFE_DOCU, @CNIM_MVTO_ORIG, @CNIM_MVTO_CNTB, 
    @CSTI_DOCU_REFE, @CSNU_DOCU_REFE, @CDFE_DOCU_REFE, @CNIM_MVTO_RETE, @CSNU_RUCS, @CSRA_SOCI ,@CSAP_PATE, @CSAP_MATE, @CSNO_NAT1, @CSNO_NAT2,@CSCO_SUNA , @CSTI_PER1

 End

Close CU_TXMVTO_CNTB_Q29_1
Deallocate CU_TXMVTO_CNTB_Q29_1

--Hallar totales
Declare  CU_TXMVTO_CNTB_Q29_3 cursor For
Select NU_RUCS,NU_DOCU_REFE , Sum(IM_MVTO_PAGO)
From #TXMVTO_CNTB_Q29
Group By  NU_RUCS, NU_DOCU_REFE

Open CU_TXMVTO_CNTB_Q29_3
Fetch CU_TXMVTO_CNTB_Q29_3 Into @VSNU_RUCS, @VSNU_DOCU_REFE, @VNSU_TOTA 
While @@Fetch_Status=0
Begin

Update #TXMVTO_CNTB_Q29
Set SU_TOTA= @VNSU_TOTA
Where  NU_RUCS= @VSNU_RUCS 
And NU_DOCU_REFE = @VSNU_DOCU_REFE

Fetch CU_TXMVTO_CNTB_Q29_3 Into @VSNU_RUCS, @VSNU_DOCU_REFE, @VNSU_TOTA 
End

Close CU_TXMVTO_CNTB_Q29_3
Deallocate CU_TXMVTO_CNTB_Q29_3    

--Construir archivo pdT

Declare  CU_TXMVTO_CNTB_Q29_2 Cursor
For
Select  NU_RUCS, NO_RAZO_SOCI , AP_PATE_AUXI , AP_MATE_AUXI , NO_NATU_AUX1 , NO_NATU_AUX2 , TI_DOCU_REFE , NU_DOCU_REFE , 
  FE_DOCU_REFE , IM_MVTO_RETE , 
  CO_SUNA ,NU_DOCU , FE_PAGO, 
  IM_MVTO_PAGO , TI_PER, SU_TOTA, IM_MVTO_PROV, FE_PROV
From  #TXMVTO_CNTB_Q29

Open CU_TXMVTO_CNTB_Q29_2
Fetch CU_TXMVTO_CNTB_Q29_2 Into 
@CSNU_RUCS, @CSRA_SOCI,  @CSAP_PATE, @CSAP_MATE, @CSNO_NAT1, @CSNO_NAT2 ,
@CSTI_DOCU_REFE,  @CSNU_DOCU_REFE , @CDFE_DOCU_REFE ,  @CNIM_MVTO_RETE , @CSCO_SUNA,
@CSNU_DOCU , @CDFE_ASTO_CNTB ,  @CNIM_MVTO_ORIG , @CSTI_PER1 ,@VNSU_TOTA, @CNIM_MVTO_PROV,
@CDFE_PROV


While @@Fetch_Status=0
Begin

Select @VNNU_LONG= NU_LONG, @VNNU_LONG_SERI= NU_LONG_SERI
From TTDOCU_CNTB
Where CO_SUNA= @CSCO_SUNA

Select @VNNU_LONG_0001 = NU_LONG,
       @VNNU_LONG_S001 = NU_LONG_SERI
From TTDOCU_CNTB
Where TI_DOCU In ( Select TI_DOCU 
                    From TTDOCU_CORR 
                    Where CO_EMPR= @ISCO_EMPR )

If @CSTI_PER1 = 'J' 
Begin
   Select  @CSAP_PATE = ''
   Select  @CSAP_MATE = '' 
   Select  @CSNO_NAT1 = ''
   Select  @CSNO_NAT2 = ''
End

If @CSTI_PER1 = 'N' 
Begin 
   Select  @CSRA_SOCI = ''
End

Insert Into TWPDTS_RETE(CO_LINE) 
 (Select(  Rtrim(Ltrim( Substring(@CSNU_RUCS,1,11))) + '|' +  
           Rtrim(Ltrim( Substring(@CSRA_SOCI,1,40)))+ '|'+ 
           Rtrim(Ltrim( Substring(@CSAP_PATE,1,20))) + '|' + 
           Rtrim(Ltrim( Substring(@CSAP_MATE,1,20))) + '|' + 
           Rtrim(Ltrim( Substring(@CSNO_NAT1 + ' ' +@CSNO_NAT2,1,20))) + '|' +

           Rtrim(Ltrim( Substring(@CSNU_DOCU_REFE,1,@VNNU_LONG_S001)))+'|'+
           Rtrim(Ltrim( Substring(@CSNU_DOCU_REFE,@VNNU_LONG_S001+2,@VNNU_LONG_0001)))+'|' +
           Convert(Varchar(10),@CDFE_DOCU_REFE, 103) +'|'+
           Rtrim(Ltrim(Str(@CNIM_MVTO_ORIG,12,2))) + '|' +
           Rtrim(Ltrim( Substring(@CSCO_SUNA,1,2))) +'|' +
           Rtrim(Ltrim( Substring( @CSNU_DOCU,1,@VNNU_LONG_SERI))) +'|' +
           Rtrim(Ltrim( Substring (@CSNU_DOCU,@VNNU_LONG_SERI+2,@VNNU_LONG))) +'|' + 
           Convert(Varchar(10),@CDFE_PROV,103) +'|' +
           Rtrim(Ltrim(Str(@CNIM_MVTO_PROV,12,2))) +'|'   ) )

Fetch CU_TXMVTO_CNTB_Q29_2  Into 
@CSNU_RUCS, @CSRA_SOCI, @CSAP_PATE, @CSAP_MATE, @CSNO_NAT1, @CSNO_NAT2 ,
@CSTI_DOCU_REFE,  @CSNU_DOCU_REFE, @CDFE_DOCU_REFE ,  @CNIM_MVTO_RETE , @CSCO_SUNA,
@CSNU_DOCU , @CDFE_ASTO_CNTB ,  @CNIM_MVTO_ORIG , @CSTI_PER1 ,@VNSU_TOTA,
@CNIM_MVTO_PROV, @CDFE_PROV

End
Close CU_TXMVTO_CNTB_Q29_2
Deallocate CU_TXMVTO_CNTB_Q29_2
        
Return
/*---------------------------------- Fin ----------------------------------*/



GO
